package com.jp.fm.dao;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.math.NumberUtils;

import com.jp.fm.HomeFinanceRunner;
import com.jp.fm.data.Category;
import com.jp.fm.data.Transaction;

public class TransactionDao {

	private static final String INSERT_SQL = "insert into transactions "
			+ " (date, account, amount, category_id, external_id, description, "
			+ "confirmed, parent_id, note) " + " VALUES(?,?,?,?,?,?,?,?,?)";
	private static final String COLUMN_DESCRIPTION = "DESCRIPTION";
	private static final String COLUMN_EXTERNAL_ID = "EXTERNAL_ID";
	private static final String COLUMN_CATEGORY_ID = "CATEGORY_ID";
	private static final String COLUMN_AMOUNT = "AMOUNT";
	private static final String COLUMN_ACCOUNT = "ACCOUNT";
	private static final String COLUMN_ID = "ID";
	private static final String COLUMN_PARENT_ID = "PARENT_ID";
	private static final String COLUMN_DATE = "DATE";
	private static final String COLUMN_CONFIRMED = "CONFIRMED";
	private static final String COLUMN_NOTE = "NOTE";
	private static final String SELECT_COLUMNS = "SELECT t.date, t.id, t.account, "
			+ "t.amount, t.category_id, t.external_id, t.description, "
			+ "t.confirmed, t.parent_id, t.note ";

	private CategoryDao categoryDao = null;

	public List<Transaction> findByAny(final Category cat,
			final Date startDate, final Date endDate, final String account,
			final String keyword, final Boolean confirmed) {

		final String selectSql = SELECT_COLUMNS + " from transactions t ";

		final String accountWhere = " account = ? ";
		final String categoryWhere = " category_id = ? ";
		final String dateStartWhere = " date >= ? ";
		final String dateEndWhere = " date <= ? ";
		final String confirmedWhere = " (confirmed = ? OR confirmed IS NULL) ";

		List<Transaction> trans = new ArrayList<Transaction>();

		Transaction tran = null;
		Connection c = null;
		PreparedStatement p = null;
		ResultSet rs = null;
		StringBuffer sb = new StringBuffer();
		boolean needWhere = false;
		List<Object> params = new ArrayList<Object>();

		if (null != cat) {
			needWhere = true;
			sb.append(categoryWhere);
			params.add(cat.getId());
		}

		if (null != startDate) {
			needWhere = true;
			if (sb.length() > 0) {
				sb.append(" and ");
			}
			sb.append(dateStartWhere);
			params.add(startDate);
		}

		if (null != endDate) {
			needWhere = true;
			if (sb.length() > 0) {
				sb.append(" and ");
			}
			sb.append(dateEndWhere);
			params.add(endDate);
		}

		if (null != account) {
			needWhere = true;
			if (sb.length() > 0) {
				sb.append(" and ");
			}
			sb.append(accountWhere);
			params.add(account);
		}

		if (null != keyword) {
			needWhere = true;
			if (sb.length() > 0) {
				sb.append(" and ");
			}
			String keywordWhere = buildKeywordWhereClause(keyword.toLowerCase());
			sb.append(keywordWhere);
		}

		if (null != confirmed) {
			needWhere = true;
			if (sb.length() > 0) {
				sb.append(" and ");
			}
			sb.append(confirmedWhere);
			params.add(HomeFinanceRunner.convertBoolToInt(confirmed));
		}

		if (needWhere) {
			sb.insert(0, " where ");
		}

		sb.insert(0, selectSql);

		try {
			c = HomeFinanceRunner.getConnection();

			p = c.prepareStatement(sb.toString());

			int i = 1;
			for (Iterator<Object> iterator = params.iterator(); iterator
					.hasNext();) {
				Object object = iterator.next();

				p.setObject(i++, object);
			}

			rs = p.executeQuery();

			while (rs.next()) {

				tran = new Transaction(rs.getDate(COLUMN_DATE),
						rs.getInt(COLUMN_ID), rs.getString(COLUMN_ACCOUNT),
						rs.getFloat(COLUMN_AMOUNT), categoryDao.findById(rs
								.getInt(COLUMN_CATEGORY_ID)),
						rs.getString(COLUMN_EXTERNAL_ID),
						rs.getString(COLUMN_DESCRIPTION),
						HomeFinanceRunner.convertIntToBool(rs
								.getInt(COLUMN_CONFIRMED)),
						rs.getInt(COLUMN_PARENT_ID));

				// If this is a child record then get its parent instead
				if (null != tran.getParentId()
						&& !Integer.valueOf(0).equals(tran.getParentId())) {
					tran = findById(tran.getParentId());
				}

				// Get any composite transactions
				List<Transaction> compositeTransactions = findByParentId(tran
						.getId());

				tran.setCompositeTransactions(compositeTransactions);

				// Also set the parent amount based off total of children
				/*
				 * if (null != compositeTransactions &&
				 * compositeTransactions.size() > 0) { Float total =
				 * Float.valueOf(NumberUtils.FLOAT_ZERO); for (Transaction
				 * transaction : compositeTransactions) { if ((null != cat &&
				 * cat.getId().equals( transaction.getCategory().getId())) ||
				 * null == cat) { total += transaction.getAmount(); } }
				 * tran.setAmount(total); }
				 */
				if (!trans.contains(tran)) {
					trans.add(tran);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			HomeFinanceRunner.closeConnection(c, p, rs);
		}

		Collections.sort(trans);

		return trans;
	}

	public void writeObject(final Transaction tran) {
		Connection c = null;
		PreparedStatement p = null;
		ResultSet rs = null;

		try {
			c = HomeFinanceRunner.getConnection();

			p = c.prepareStatement("update transactions set category_id = ?, "
					+ " confirmed = ?, amount = ? where id = ?");
			p.setInt(HomeFinanceRunner.DB_COLUMN_FIRST, tran.getCategory()
					.getId());
			p.setInt(HomeFinanceRunner.DB_COLUMN_SECOND,
					HomeFinanceRunner.convertBoolToInt(tran.isConfirmed()));
			p.setFloat(HomeFinanceRunner.DB_COLUMN_THIRD, tran.getAmount());
			p.setInt(HomeFinanceRunner.DB_COLUMN_FOURTH, tran.getId());

			p.executeUpdate();

			c.commit();

		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			HomeFinanceRunner.closeConnection(c, p, rs);
		}
	}

	protected Integer insertTransaction(final Transaction tran)
			throws SQLException {
		Connection c = null;
		PreparedStatement p = null;
		ResultSet rs = null;
		Integer ret = null;

		try {
			c = HomeFinanceRunner.getConnection();

			p = c.prepareStatement("INSERT INTO TRANSACTIONS "
					+ "( DATE, ACCOUNT, AMOUNT, CATEGORY_ID, DESCRIPTION, "
					+ "EXTERNAL_ID, CONFIRMED) " + "VALUES (?,?,?,?,?,?,0)");
			p.setDate(HomeFinanceRunner.DB_COLUMN_FIFTH, new java.sql.Date(tran
					.getDate().getTime()));
			p.setString(HomeFinanceRunner.DB_COLUMN_SECOND, tran.getAccount());
			p.setFloat(HomeFinanceRunner.DB_COLUMN_THIRD, tran.getAmount());
			p.setInt(HomeFinanceRunner.DB_COLUMN_FOURTH, tran.getCategory()
					.getId());
			p.setString(HomeFinanceRunner.DB_COLUMN_FIFTH,
					tran.getDescription());
			p.setString(HomeFinanceRunner.DB_COLUMN_SIXTH, tran.getExternalId());

			p.executeUpdate();
			c.commit();

			// Get the generated ID
			p = c.prepareStatement("CALL IDENTITY()");
			rs = p.executeQuery();
			rs.next();
			ret = rs.getInt(1);
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			HomeFinanceRunner.closeConnection(c, p, rs);
		}

		return ret;
	}

	public void writeCompositeTransactions(final Transaction tran) {
		Connection c = null;
		PreparedStatement p = null;
		ResultSet rs = null;

		List<Transaction> trans = tran.getCompositeTransactions();
		for (Transaction transaction : trans) {

			if (null == transaction.getId()) {
				try {
					c = HomeFinanceRunner.getConnection();

					p = c.prepareStatement(INSERT_SQL);
					p.setDate(HomeFinanceRunner.DB_COLUMN_FIRST,
							new java.sql.Date(transaction.getDate().getTime()));
					p.setString(HomeFinanceRunner.DB_COLUMN_SECOND,
							transaction.getAccount());
					p.setFloat(HomeFinanceRunner.DB_COLUMN_THIRD,
							transaction.getAmount());
					p.setInt(HomeFinanceRunner.DB_COLUMN_FOURTH, transaction
							.getCategory().getId());
					p.setString(HomeFinanceRunner.DB_COLUMN_FIFTH,
							transaction.getExternalId());
					p.setString(HomeFinanceRunner.DB_COLUMN_SIXTH,
							transaction.getDescription());
					p.setInt(HomeFinanceRunner.DB_COLUMN_SEVENTH,
							HomeFinanceRunner.convertBoolToInt(transaction
									.isConfirmed()));
					p.setInt(HomeFinanceRunner.DB_COLUMN_EIGHTH,
							transaction.getParentId());
					p.setString(HomeFinanceRunner.DB_COLUMN_NINETH,
							transaction.getNote());

					p.executeUpdate();
					c.commit();

					p.close();

					// Get the generated ID
					p = c.prepareStatement("CALL IDENTITY()");
					rs = p.executeQuery();
					rs.next();
					int identity = rs.getInt(1);
					transaction.setId(identity);

				} catch (SQLException e) {
					e.printStackTrace();
				} catch (IOException e) {
					e.printStackTrace();
				} finally {
					HomeFinanceRunner.closeConnection(c, p, rs);
				}
			}
		}
	}

	public Transaction findById(final Integer id) {

		Transaction tran = null;
		Connection c = null;
		PreparedStatement p = null;
		ResultSet rs = null;

		try {
			c = HomeFinanceRunner.getConnection();

			p = c.prepareStatement(SELECT_COLUMNS + " FROM transactions t "
					+ " where id = ?");
			p.setInt(1, id);

			rs = p.executeQuery();

			if (rs.next()) {

				tran = new Transaction(rs.getDate(COLUMN_DATE),
						rs.getInt(COLUMN_ID), rs.getString(COLUMN_ACCOUNT),
						rs.getFloat(COLUMN_AMOUNT), categoryDao.findById(rs
								.getInt(COLUMN_CATEGORY_ID)),
						rs.getString(COLUMN_EXTERNAL_ID),
						rs.getString(COLUMN_DESCRIPTION),
						HomeFinanceRunner.convertIntToBool(rs
								.getInt(COLUMN_CONFIRMED)),
						rs.getInt(COLUMN_PARENT_ID));

				tran.setNote(rs.getString(COLUMN_NOTE));

				// If this is a child record then get its parent instead
				if (null != tran.getParentId()
						&& !Integer.valueOf(0).equals(tran.getParentId())) {
					tran = findById(tran.getParentId());
				}

				// Get any composite transactions
				List<Transaction> compositeTransactions = findByParentId(tran
						.getId());

				tran.setCompositeTransactions(compositeTransactions);

				// Also set the parent amount based off total of children
				if (null != compositeTransactions
						&& compositeTransactions.size() > 0) {
					Float total = Float.valueOf(NumberUtils.FLOAT_ZERO);
					for (Transaction transaction : compositeTransactions) {
						total += transaction.getAmount();
					}
					tran.setAmount(total);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			HomeFinanceRunner.closeConnection(c, p, rs);
		}

		return tran;
	}

	public List<Transaction> findByAccountType(final String accountType) {

		List<Transaction> trans = new ArrayList<Transaction>();

		Transaction tran = null;
		Connection c = null;
		PreparedStatement p = null;
		ResultSet rs = null;

		try {
			c = HomeFinanceRunner.getConnection();

			p = c.prepareStatement(SELECT_COLUMNS
					+ " FROM transactions t, account a, "
					+ "account_type p where p.name = ? and p.id = a.account_type "
					+ "and a.name = t.account and t.parent_id IS NULL");
			p.setString(1, accountType);

			rs = p.executeQuery();

			while (rs.next()) {

				tran = new Transaction(rs.getDate(COLUMN_DATE),
						rs.getInt(COLUMN_ID), rs.getString(COLUMN_ACCOUNT),
						rs.getFloat(COLUMN_AMOUNT), categoryDao.findById(rs
								.getInt(COLUMN_CATEGORY_ID)),
						rs.getString(COLUMN_EXTERNAL_ID),
						rs.getString(COLUMN_DESCRIPTION),
						HomeFinanceRunner.convertIntToBool(rs
								.getInt(COLUMN_CONFIRMED)),
						rs.getInt(COLUMN_PARENT_ID));

				tran.setNote(rs.getString(COLUMN_NOTE));

				// If this is a child record then get its parent instead
				if (null != tran.getParentId()
						&& !Integer.valueOf(0).equals(tran.getParentId())) {
					tran = findById(tran.getParentId());
				}

				// Get any composite transactions
				List<Transaction> compositeTransactions = findByParentId(tran
						.getId());

				tran.setCompositeTransactions(compositeTransactions);

				// Also set the parent amount based off total of children
				if (null != compositeTransactions
						&& compositeTransactions.size() > 0) {
					Float total = Float.valueOf(NumberUtils.FLOAT_ZERO);
					for (Transaction transaction : compositeTransactions) {
						total += transaction.getAmount();
					}
					tran.setAmount(total);
				}

				if (!trans.contains(tran)) {
					trans.add(tran);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			HomeFinanceRunner.closeConnection(c, p, rs);
		}

		Collections.sort(trans);

		return trans;
	}

	public List<Transaction> findByParentId(final Integer id) {

		List<Transaction> trans = new ArrayList<Transaction>();
		Transaction tran = null;
		Connection c = null;
		PreparedStatement p = null;
		ResultSet rs = null;

		try {
			c = HomeFinanceRunner.getConnection();

			p = c.prepareStatement(SELECT_COLUMNS + " FROM transactions t "
					+ " where parent_id = ?");
			p.setInt(1, id);

			rs = p.executeQuery();

			while (rs.next()) {

				tran = new Transaction(rs.getDate(COLUMN_DATE),
						rs.getInt(COLUMN_ID), rs.getString(COLUMN_ACCOUNT),
						rs.getFloat(COLUMN_AMOUNT), categoryDao.findById(rs
								.getInt(COLUMN_CATEGORY_ID)),
						rs.getString(COLUMN_EXTERNAL_ID),
						rs.getString(COLUMN_DESCRIPTION),
						HomeFinanceRunner.convertIntToBool(rs
								.getInt(COLUMN_CONFIRMED)),
						rs.getInt(COLUMN_PARENT_ID));

				tran.setNote(rs.getString(COLUMN_NOTE));

				trans.add(tran);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			HomeFinanceRunner.closeConnection(c, p, rs);
		}

		return trans;
	}

	private static String buildKeywordWhereClause(final String keyword) {

		String ret = "";
		final String whereStart = " LCASE(t.DESCRIPTION) LIKE '%";
		final String whereEnd = "%'";

		// Split keywords by spaces or quotes
		String inStr = keyword;
		List<String> searchKeywords = new ArrayList<String>();

		// Split by quotes first
		Pattern pattern = Pattern.compile("\"\\w*\\s*\\w*\"");
		Matcher matcher = pattern.matcher(inStr);
		String matchStr = null;
		while (matcher.find()) {
			matchStr = matcher.group();
			inStr = StringUtils.remove(inStr, matchStr);
			matchStr = matchStr.replace("\"", "");
			searchKeywords.add(matchStr);
		}

		// Split by spaces
		String[] strs = inStr.split(" ");
		for (String string : strs) {
			searchKeywords.add(string);
		}

		// Build out where clause
		int counter = 0;
		for (String kWord : searchKeywords) {
			if (counter > 0 && counter < searchKeywords.size()) {
				ret += " OR ";
			}
			ret += whereStart + kWord + whereEnd;
			counter++;
		}

		ret = " (" + ret + ") ";

		return ret;
	}

	public final void setCategoryDao(final CategoryDao categoryDao) {
		this.categoryDao = categoryDao;
	}
}
